 create or replace view VIEW_YBXX_ZJYLB as
                select * from (select t.*, level as tree_level,SYS_CONNECT_BY_PATH(t.PK_FINANCEORG,'\') as tree_path
                      from (

                            select code,
                                    name,
                                    pk_financeorg,
                                    pk_fatherorg,
                                    pk_corp,
                                    pk_group,
                                    enablestate
                              from (SELECT org_corp.code,
                                            org_corp.name,
                                            org_corp.pk_corp pk_financeorg,
                                            org_corp.pk_fatherorg,
                                            org_corp.pk_corp,
                                            org_corp.pk_group,
                                            org_financeorg.enablestate
                                       FROM org_corp
                                       LEFT outer JOIN org_financeorg
                                         ON org_corp.pk_corp =
                                            org_financeorg.pk_financeorg
                                      WHERE org_corp.pk_corp IN
                                            (SELECT pk_org
                                               FROM org_orgs
                                              WHERE orgtype5 = 'Y')
                                     UNION
                                     SELECT org_financeorg.code,
                                            org_financeorg.name,
                                            org_financeorg.pk_financeorg,
                                            org_orgs.pk_corp pk_fatherorg,
                                            org_orgs.pk_corp,
                                            org_financeorg.pk_group,
                                            org_financeorg.enablestate
                                       FROM org_financeorg
                                       LEFT outer JOIN org_orgs
                                         ON org_financeorg.pk_financeorg =
                                            org_orgs.pk_org
                                      WHERE orgtype5 = 'Y'
                                        AND org_financeorg.pk_financeorg <>
                                            org_orgs.pk_corp) org_financeorg_temp
                             where 11 = 11
                               and (enablestate = 2)
                               and ((pk_group =
                                   (select pk_group
                                        from org_corp
                                       where pk_fatherorg = '~')))
                             order by code) t
                     start with pk_financeorg =
                                (select pk_corp
                                   from org_corp
                                  where pk_fatherorg = '~')
                    connect by prior pk_financeorg = pk_fatherorg
                     order by level) t1
                     left JOIN
                     (
                     select corp,
  sum(case when typename = '结算中心' then local_amount else 0 end) JIE_SUAN_ZHONG_XIN,
  sum(case when typename = '财务公司' then local_amount else 0 end) CAI_WU_GONG_SI,
  sum(case when typename = '国内银行' then local_amount else 0 end)+ sum(case when typename = '国内银行受限' then local_amount else 0 end) WAI_BU_YIN_HANG,
  sum(case when typename = '国内银行受限' then local_amount else 0 end) GUO_NEI_YIN_HANG_SHOU_XIAN,
  sum(case when typename = '财务公司' then local_amount else 0 end) + sum(case when typename = '国内银行' then local_amount else 0 end) REN_MIN_BI_ZI_JIN_YU_E,
  sum(case when typename = '国外银行' then local_amount else 0 end) YIN_HANG_CUN_KUAN_WAI_BI,
  sum(case when typename = '现金' then local_amount else 0 end) XIAN_JIN,
  sum(case when typename = '结算中心' then local_amount else 0 end) + sum(case when typename = '财务公司' then local_amount else 0 end) +
  sum(case when typename = '国内银行' then local_amount else 0 end) + sum(case when typename = '国内银行受限' then local_amount else 0 end) +
  sum(case when typename = '国外银行' then local_amount else 0 end) + sum(case when typename = '现金' then local_amount else 0 end) ZI_JIN_YU_E_ZHANG_MIAN_YU_E
   from (
select corp,typename, sum(primal_amount) as primal_amount, sum(local_amount) as local_amount
  from (
  select t1.*,
               t4.name,
               case
                 when t4.name = '财务公司' then
                  '财务公司'
                 when t4.name = '结算中心' then
                  '结算中心'
                 when t4.name = '国外银行' then
                  '国外银行'
                 when nvl(t4.name,'现金'）= '现金' then
                   '现金'
                 when t3.DEF13 =  (select pk_defdoc
   from bd_defdoc
  where PK_DEFDOCLIST in (select regexp_substr(REFTYPE, '[^:]+', 1, 1)  --, ITEMKEY
                            from pub_billtemplet_b
                           where pk_billtemplet in
                                 (select pk_billtemplet
                                    from pub_billtemplet
                                   where BILL_TEMPLETNAME = '银行账户基本信息')
                             and DEFAULTSHOWNAME = '是否受限')
    and NAME = '是') then
                   '国内银行受限'
                 else
                  '国内银行'
               end as typename

          from (

          select ut.corp,
                       ut.pk_currtype,
                       ut.pk_account,
                       sum(primal_amount) primal_amount,
                       sum(local_amount) local_amount
                  from ((select pk_org      corp,
                                pk_currtype pk_currtype,
                                pk_account  pk_account,
                                init_primal primal_amount,
                                init_local  local_amount
                           from cmp_initdata
                          where 11=11

                            and dr = 0
                            and formcode in (0,1)) union all
                        (select pk_org corp,
                                pk_curr pk_currtype,
                                pk_account pk_account,
                                sum(nvl(recmoney, 0)) - sum(nvl(paymoney, 0)) primal_amount,
                                sum(nvl(olcrecmoney, 0)) -
                                sum(nvl(olcpaymoney, 0)) local_amount
                           from cmp_bankaccdetail
                          where  11=11
                            and useFlag = 1
                            and fundformcode in (0,1)
                            and dr = 0
                          group by pk_org, pk_curr, pk_account)) ut
                 group by ut.corp, ut.pk_currtype, ut.pk_account

                 ) t1
          left join bd_bankaccsub t2
            on t1.pk_account = t2.pk_bankaccsub
          left join bd_bankaccbas t3
            on t2.pk_bankaccbas = t3.pk_bankaccbas
          left join bd_banktype t4
            on t3.pk_banktype = t4.pk_banktype
            )
 group by corp,typename
 )  group by corp
  ) t2 ON t1.pk_financeorg = t2.corp;

